What is sql injection and how can it cause in ASP.Net c#?

SQL injection is a malicious code some user might inject SQL commands through strings for execution. It seems to very dangerous to the security of application.

We will be using employee table

Step 1: Create a table using the following script with data:

CREATE TABLE[dbo].[Employee](
      [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](50) NULL,
      [Gender] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
 CONSTRAINT[PK_tbl_Employee] PRIMARY KEY CLUSTERED 
(
      [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON[PRIMARY]
GO
SET IDENTITY_INSERT[dbo].[Employee] ON
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (1, N'Thivan', N'male', N'tirunelveli')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (2, N'Rasik', N'male', N'Tuticorin')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (3, N'Usman', N'male', N'tirunelveli')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (4, N'karishma', N'female', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (5, N'chaitrali', N'female ', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (6, N'mansoor', N'male', N'gujarat')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (7, N'mydeen', N'male', N'chennai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (8, N'zubair', N'male', N'melapalayam')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (9, N'matkar', N'male', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (10, N'Rahim', N'male', N'mumbai')
SET IDENTITY_INSERT[dbo].[Employee] OFF

Step 2: Copy and paste the following code.

Default.aspx:

    <table style="border: 1px solid #e2e2e2; font-family: Arial">
        <tr>
            <td>
               <asp:TextBox ID="ProductNameTextBox" runat="server"></asp:TextBox>
                <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            </td>
        </tr>
    </table>

Default.aspx.cs:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.DataVisualization.Charting;
using System.Web.UI.WebControls;
 
public partial class _Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
           BindData();
        }
    }

    private void BindData()
    {
        //Create the connection object
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ShoppingZone"].ConnectionString); ;
        // Pass the connection to thecommand object, so the command object knows on which
        // connection to execute the command
        SqlCommand cmd = new SqlCommand("Select * from employee where Name like '%" + ProductNameTextBox.Text + "%'", connection);
        // Open the connection. Otherwiseyou get a runtime error. An open connection is
        // required to execute the command
       connection.Open();
       GridView1.DataSource = cmd.ExecuteReader();
       GridView1.DataBind();
       connection.Close();
    }
 
    protected void btnSearch_Click(object sender, EventArgs e)
    {
       BindData();
    }
}

Some user may type dangerous sql queries into the textbox, which in turn will be executed by the application on the database.

If user type below code on the textbox,

t'; Delete from Employee--

The entire data will be deleted from the employee table. This is called SQL injection attack.

 Output: